﻿ //class IConnectionSource

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;
using System.Xml;


namespace SqlToolkit
{
    #region IDB
    /// <summary>This provides a wrapper for generic database operations.</summary>
    [Serializable]
    public sealed class IDB
    {
        private IDbConnection conn;

        #region Nested Class: SqlAccessor
        /// <summary> Provide a higher level execution of IDb routines than SqlProcedure or SqlText alone.</summary>
        [Serializable]
        public sealed class IDbAccessor
        {
            #region Nested Class: SqlProcedure
            /// <summary>Provides methods to access and manipulate a IDb DataSource Database through Stored Procedures.</summary>
            [Serializable]
            public sealed class SqlProcedure
            {
                #region Construction
                IDB conn = null;
                public SqlProcedure(IDB connection) { this.conn = connection; }
                #endregion
                #region ExecuteReader
                public System.Data.IDataReader ExecuteReader(string procedure)
                {
                    if (null == conn) return null;
                    System.Data.IDataReader ret = null;
                    IDbCommand cmd = null;

                    try
                    {
                        cmd = conn.CreateCommand();
                        cmd.CommandText = procedure;
                        cmd.CommandType = CommandType.StoredProcedure;

                        if (null == cmd)
                            return null;

                        ret = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    }
                    catch (Exception ex)
                    {
                        IDbException dbEx = new IDbException(procedure, ex);
                        throw (dbEx);
                    }

                    return ret;
                }

                public System.Data.IDataReader ExecuteReader(string procedure, IDataParameter param)
                {
                    if (null == conn) return null;
                    System.Data.IDataReader ret = null;
                    IDbCommand cmd = null;

                    try
                    {
                        cmd = conn.CreateCommand(procedure, param);

                        if (null == cmd)
                            return null;

                        ret = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    }
                    catch (Exception ex)
                    {
                        IDbException dbEx = new IDbException(procedure, ex);
                        throw (dbEx);
                    }

                    return ret;
                }
                public System.Data.IDataReader ExecuteReader(string procedure, IDataParameter[] param)
                {
                    if (null == conn) return null;
                    System.Data.IDataReader ret = null;
                    IDbCommand cmd = null;

                    try
                    {
                        cmd = conn.CreateCommand(procedure, param);

                        if (null == cmd)
                            return null;

                        ret = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    }
                    catch (Exception ex)
                    {
                        IDbException dbEx = new IDbException(procedure, ex);
                        throw (dbEx);
                    }

                    return ret;
                }
                #endregion
                #region ExecuteScalar
                public object ExecuteScalar(string procedure)
                {
                    if (null == conn) return null;
                    object ret = null;
                    IDbCommand cmd = null;

                    try
                    {
                        cmd = conn.CreateCommand(procedure, CommandType.StoredProcedure);

                        if (null == cmd)
                            return null;

                        ret = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        IDbException dbEx = new IDbException(procedure, ex);
                        throw (dbEx);
                    }
                    finally
                    {
                        cmd.Connection.Close();
                        cmd.Dispose();
                        cmd = null;
                    }

                    return ret;
                }
                public object ExecuteScalar(string procedure, IDataParameter param)
                {
                    if (null == conn) return null;
                    object ret = null;
                    IDbCommand cmd = null;
                    
                    try
                    {
                        cmd = conn.CreateCommand(procedure, param);

                        if (null == cmd)
                            return null;

                        ret = cmd.ExecuteScalar();
                    }
                    catch (Exception ex) 
                    {
                        IDbException dbEx = new IDbException(procedure, ex);
                        throw (dbEx); 
                    }
                    finally
                    {
                        cmd.Connection.Close();
                        cmd.Dispose();
                        cmd = null;
                    }

                    return ret;
                }
                public object ExecuteScalar(string procedure, IDataParameter[] param)
                {
                    if (null == conn) return null;
                    object ret = null;
                    IDbCommand cmd = null;

                    try
                    {
                        cmd = conn.CreateCommand(procedure, param);

                        if (null == cmd)
                            return null;

                        ret = cmd.ExecuteScalar();
                    }
                    catch (Exception ex) 
                    {
                        IDbException dbEx = new IDbException(procedure, ex);
                        throw (dbEx); 
                    }
                    finally
                    {
                        cmd.Connection.Close();
                        cmd.Dispose();
                        cmd = null;
                    }

                    return ret;
                }
                #endregion
                #region ExecuteNonQuery
                public void ExecuteNonQuery(string procedure)
                {
                    if (null == conn) return;
                    IDbCommand cmd = null;

                    try
                    {
                        cmd = conn.CreateCommand(procedure, CommandType.StoredProcedure);

                        if (null == cmd)
                            return;

                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex) 
                    {
                        IDbException dbEx = new IDbException(procedure, ex);
                        throw (dbEx); 
                    } 
                    finally
                    {
                        cmd.Connection.Close();
                        cmd.Dispose();
                        cmd = null;
                    }

                }
                public void ExecuteNonQuery(string procedure, IDataParameter param)
                {
                    if (null == conn) return;
                    IDbCommand cmd = null;

                    try
                    {
                        cmd = conn.CreateCommand(procedure, param);

                        if (null == cmd)
                            return;

                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        IDbException dbEx = new IDbException(procedure, ex);
                        throw (dbEx);
                    }
                    finally
                    {
                        cmd.Connection.Close();
                        cmd.Dispose();
                        cmd = null;
                    }

                }
                public void ExecuteNonQuery(string procedure, IDataParameter[] param)
                {
                    if (null == conn) return;
                    IDbCommand cmd = null;

                    try
                    {
                        cmd = conn.CreateCommand(procedure, param);

                        if (null == cmd)
                            return;

                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        IDbException dbEx = new IDbException(procedure, ex);
                        throw (dbEx);
                    }
                    finally
                    {
                        if (null != cmd)
                        {
                            cmd.Connection.Close();
                            cmd.Dispose();
                            cmd = null;
                        }
                    }

                }
                #endregion
            }
            #endregion
            #region Nested Class: SqlText
            /// <summary>Provides methods to access and manipulate a IDb DataSource Database through IDb  text.</summary>
            [Serializable]
            public sealed class SqlText
            {
                #region Construction
                IDB conn = null;
                public SqlText(IDB connection) { this.conn = connection; }
                #endregion
                #region ExecuteReader
                public System.Data.IDataReader ExecuteReader(string sql)
                {
                    if (null == conn) return null;
                    System.Data.IDataReader ret = null;
                    IDbCommand cmd = null;

                    try
                    {
                        cmd = conn.CreateCommand(sql, CommandType.Text);

                        if (null == cmd)
                            return null;

                        ret = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    }
                    catch (Exception ex)
                    {
                        IDbException dbEx = new IDbException(sql, ex);
                        throw (dbEx);
                    }

                    return ret;
                }
                public System.Data.IDataReader ExecuteReader(string sql, IDataParameter param)
                {
                    if (null == conn) return null;
                    System.Data.IDataReader ret = null;
                    IDbCommand cmd = null;

                    try
                    {
                        cmd = conn.CreateCommand(sql, CommandType.Text);

                        if (null == cmd)
                            return null;

                        cmd.Parameters.Add(param);

                        ret = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    }
                    catch (Exception ex)
                    {
                        IDbException dbEx = new IDbException(sql, ex);
                        throw (dbEx);
                    }

                    return ret;
                }
                public System.Data.IDataReader ExecuteReader(string sql, IDataParameter[] @params)
                {
                    if (null == conn) return null;
                    System.Data.IDataReader ret = null;
                    IDbCommand cmd = null;

                    try
                    {
                        cmd = conn.CreateCommand(sql, CommandType.Text);

                        if (null == cmd)
                            return null;

                        if (null != @params && @params.Length > 0)
                        {
                            foreach (IDataParameter p in @params)
                            {
                                cmd.Parameters.Add(p);
                            }
                        }

                        ret = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    }
                    catch (Exception ex)
                    {
                        IDbException dbEx = new IDbException(sql, ex);
                        throw (dbEx);
                    }

                    return ret;
                }
                #endregion
                #region ExecuteScalar
                public object ExecuteScalar(string sql)
                {
                    if (null == conn) return null;
                    object ret = null;
                    IDbCommand cmd = null;

                    try
                    {
                        cmd = conn.CreateCommand(sql, CommandType.Text);

                        if (null == cmd)
                            return null;

                        ret = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        IDbException dbEx = new IDbException(sql, ex);
                        throw (dbEx);
                    }
                    finally
                    {
                        cmd.Connection.Close();
                        cmd.Dispose();
                        cmd = null;
                    }

                    return ret;
                }
                public object ExecuteScalar(string sql, IDataParameter param)
                {
                    if (null == conn) return null;
                    object ret = null;
                    IDbCommand cmd = null;

                    try
                    {
                        cmd = conn.CreateCommand(sql, CommandType.Text);

                        if (null == cmd)
                            return null;


                        cmd.Parameters.Add(param);

                        ret = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        IDbException dbEx = new IDbException(sql, ex);
                        throw (dbEx);
                    }
                    finally
                    {
                        cmd.Connection.Close();
                        cmd.Dispose();
                        cmd = null;
                    }

                    return ret;
                }
                public object ExecuteScalar(string sql, IDataParameter[] @params)
                {
                    if (null == conn) return null;
                    object ret = null;
                    IDbCommand cmd = null;

                    try
                    {
                        cmd = conn.CreateCommand(sql, CommandType.Text);

                        if (null == cmd)
                            return null;


                        if (null != @params && @params.Length > 0)
                        {
                            foreach (IDataParameter p in @params)
                            {
                                cmd.Parameters.Add(p);
                            }
                        }

                        ret = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        IDbException dbEx = new IDbException(sql, ex);
                        throw (dbEx);
                    }
                    finally
                    {
                        cmd.Connection.Close();
                        cmd.Dispose();
                        cmd = null;
                    }

                    return ret;
                }
                #endregion
                #region ExecuteNonQuery
                public void ExecuteNonQuery(string sql)
                {
                    if (null == conn) return;
                    IDbCommand cmd = null;

                    try
                    {
                        cmd = conn.CreateCommand(sql, CommandType.Text);

                        if (null == cmd)
                            return;

                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        IDbException dbEx = new IDbException(sql, ex);
                        throw (dbEx);
                    }
                    finally
                    {
                        cmd.Connection.Close();
                        cmd.Dispose();
                        cmd = null;
                    }

                }
                public void ExecuteNonQuery(string sql, IDataParameter param)
                {
                    if (null == conn) return;
                    IDbCommand cmd = null;

                    try
                    {
                        cmd = conn.CreateCommand(sql, CommandType.Text);

                        if (null == cmd)
                            return;

                        cmd.Parameters.Add(param);
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        IDbException dbEx = new IDbException(sql, ex);
                        throw (dbEx);
                    }
                    finally
                    {
                        cmd.Connection.Close();
                        cmd.Dispose();
                        cmd = null;
                    }

                }
                public void ExecuteNonQuery(string sql, IDataParameter[] @params)
                {
                    if (null == conn) return;
                    IDbCommand cmd = null;

                    try
                    {
                        cmd = conn.CreateCommand(sql, CommandType.Text);

                        if (null == cmd)
                            return;

                        if (null != @params && @params.Length > 0)
                        {
                            foreach (IDataParameter p in @params)
                            {
                                cmd.Parameters.Add(p);
                            }
                        }

                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        IDbException dbEx = new IDbException(sql, ex);
                        throw (dbEx);
                    }
                    finally
                    {
                        cmd.Connection.Close();
                        cmd.Dispose();
                        cmd = null;
                    }

                }
                #endregion
            }
            #endregion
            #region Construction
            IDB conn = null;
            public IDbAccessor(IDB connection)
            {
                conn = connection;
                proc = new SqlProcedure(conn);
                sqlTxt = new SqlText(conn);
            }
            #endregion
            #region Helper Properties
            SqlProcedure proc = null;
            public SqlProcedure ExecuteProcedure { get { return proc; } }
            SqlText sqlTxt = null;
            public SqlText ExecuteSqlText { get { return sqlTxt; } }
            #endregion
        }
        #endregion
        #region Construction
        public IDB(IDbConnection connection)
        {
            conn = connection;
            db_hit = new IDbAccessor(this);
        }
        #endregion
        #region Properties
        #region Accessor
        IDbAccessor db_hit;
        /// <summary>
        /// Provides properties and methods to be used when accesing the database.
        /// </summary>
        public IDbAccessor DbAccessor { get { return db_hit; } }
        #endregion
        public IDbConnection Connection
        {
            get
            {
                return conn;
            }
        }
        #endregion
        #region Methods

        #region CleanConnection
        /// <summary>
        /// Close, dispose, and set an IDbConnection to null.
        /// </summary>
        /// <param name="conn"></param>
        public void CleanConnection(IDbConnection conn)
        {
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
                conn = null;
            }
        }
        #endregion
        #region CleanCommand
        /// <summary>
        /// Dispose of a IDbCommand and set it equal to null.
        /// </summary>
        /// <param name="cmd">The IDbCommand.</param>
        public void CleanCommand(IDbCommand cmd)
        {
            if (cmd != null)
            {
                if (null != cmd.Connection)
                {   //not sure this is needed but better safe than sorry.
                    CleanConnection(cmd.Connection);
                }

                cmd.Dispose();
                cmd = null;
            }
        }
        #endregion
        #region CleanDataReader
        /// <summary>
        /// Close a System.Data.IDataReader and set it equal to null. 
        /// If the reader was created with CommandBehavior.CloseConnection the 
        /// connection associated with the reader will be closed.
        /// </summary>
        /// <param name="r">The System.Data.IDataReader.</param>
        public void CleanDataReader(System.Data.IDataReader r)
        {
            if (r != null)
            {
                r.Close();
                r = null;
            }
        }
        #endregion
        #region CleanDataAdapter
        /// <summary>
        /// Dispose of a IDbDataAdapter and set it to null.
        /// </summary>
        /// <param name="adapt">The IDbDataAdapter to be cleaned.</param>
        public void CleanDataAdapter(IDbDataAdapter adapt)
        {
            if (adapt != null)
            {
                u.clean(adapt);
            }
        }
        #endregion
        #region CleanTransaction
        /// <summary>
        /// Dispose of a IDbTransaction nd set it qua to null.
        /// </summary>
        /// <param name="trans"></param>
        public void CleanTransaction(IDbTransaction trans)
        {
            if (trans != null)
            {
                trans.Dispose();
                trans = null;
            }
        }
        #endregion

        #endregion

       
        #region CreateCommand
        /// <summary>
        /// Gets a IDbCommand object initialized to this connection.
        /// </summary>
        /// <returns>A IDbCommand or null.</returns>
        public IDbCommand CreateCommand()
        {
            IDbCommand cmd = null;

            try
            {
                if (null == conn)
                    return cmd;

                cmd = conn.CreateCommand();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);//return null if error
                cmd = null;
            }

            return cmd;
        }
        /// <summary>
        /// Gets a IDbCommand object initialized to this connection and set to the in passed command.
        /// </summary>
        /// <param name="command_text">The sql, table name or stored procedure name to use.</param>
        /// <param name="type">The type of command text passed in.</param>
        /// <returns>A IDbCommand or null.</returns>
        public IDbCommand CreateCommand(string command_text, CommandType type)
        {
            IDbCommand cmd = null;

            if (null == conn)
                return cmd;

            cmd = conn.CreateCommand();
            cmd.CommandText = command_text;
            cmd.Connection = conn;
            cmd.CommandType = type;

            return cmd;
        }

        /// <summary>
        /// Gets a IDbCommand initialized to this connection and set to use the in passed procedure.
        /// </summary>
        /// <param name="procedure_name">Stored procedure name.</param>
        /// <param name="param">Parameter to be used by the procedure.</param>
        /// <returns>A IDbCommand or null.</returns>
        public IDbCommand CreateCommand(string procedure_name, IDataParameter param)
        {
            IDbCommand cmd = CreateCommand(procedure_name, CommandType.StoredProcedure);

            if (null == cmd)
                return cmd;

            if (null != param)
                cmd.Parameters.Add(param);

            return cmd;
        }

        /// <summary>
        /// Gets a IDbCommand initialized to this connection and set to use the in passed procedure.
        /// </summary>
        /// <param name="procedure_name">Stored procedure name.</param>
        /// <param name="param">Parameters to be used by the procedure.</param>
        /// <returns>A IDbCommand or null.</returns>
        public IDbCommand CreateCommand(string procedure_name, IDataParameter[] param)
        {
            IDbCommand cmd = CreateCommand(procedure_name, CommandType.StoredProcedure);

            if (null == cmd)
                return cmd;

            if (param.Length > 0)
            {
                for (int i = 0; i < param.Length; i++)
                    cmd.Parameters.Add(param[i]);
            }

            return cmd;
        }
        #endregion
        #region CreateParameter
        public DbType GetDbType(object value)
        {

            DbType type = DbType.String;
            string typeName = value.GetType().FullName;
            switch (typeName)
            {
                case "System.Int16":
                    type = DbType.Int16;
                    break;
                case "System.Int32":
                    type = DbType.Int32;
                    break;
                case "System.Int64":
                    type = DbType.Int64;
                    break;
                case "System.UInt16":
                    type = DbType.UInt16;
                    break;
                case "System.UInt32":
                    type = DbType.UInt32;
                    break;
                case "System.UInt64":
                    type = DbType.UInt64;
                    break;
                case "System.Decimal":
                    type = DbType.Decimal;
                    break;
                case "System.Double":
                    type = DbType.Double;
                    break;
                case "System.Single":
                    type = DbType.Single;
                    break;
                case "System.String":
                    type = DbType.String;
                    break;
                case "System.DateTime":
                    type = DbType.DateTime;
                    break;
                case "System.Byte[]":
                    type = DbType.Binary;
                    break;
                case "System.Byte":
                    type = DbType.Byte;
                    break;
                default:
                    throw new Exception("The in passed value is of a type that is not currently supported for dynamic determination.");
            }

            return type;
        }

        public IDataParameter CreateParameter(string name, object value, System.Data.ParameterDirection direction, DbType type)
        {
            if (null == name || name.Trim().Length == 0) return null;
            if (null == value) return null;

            IDbCommand cmd = conn.CreateCommand();
            IDataParameter ret = cmd.CreateParameter();

            ret.ParameterName = name;
            ret.Direction = direction;
            ret.DbType = type;
            ret.Value = value;

            return ret;
        }

        public IDataParameter CreateParameter(string name, object value)
        {
            if (null == name || name.Trim().Length == 0) return null;
            if (null == value) return null;

            DbType type = GetDbType(value);
            return CreateParameter(name, value, ParameterDirection.Input, type);
        }
        #endregion

    }
    #endregion
}

internal class u
{
    public static void clean(object obj)
    {
        
        if (obj is IDisposable) ((IDisposable)obj).Dispose();

        obj = null;
    }
}

//works
#region CreateFromString
        ///// <summary>
        ///// Attempts to create a ConnectionSource by parsing an in passed connection string.
        ///// </summary>
        ///// <param name="conn_str">Connection string.</param>
        ///// <returns>A ConnectionSource or null.</returns>
        //public static IDB CreateFromString(string conn_str)
        //{
        //    IDB ret = new IDB();

        //    string[] split01 = conn_str.Split(";".ToCharArray());
        //    foreach (string s in split01)
        //    {
        //        string[] split02 = s.Split("=".ToCharArray());
        //        try
        //        {
        //            switch (split02[0].ToLower())
        //            {
        //                case "data source":
        //                case "dsn":
        //                    ret.dsn = split02[1];
        //                    break;
        //                case "user id":
        //                case "uid":
        //                    ret.user = split02[1];
        //                    break;
        //                case "password":
        //                case "pwd":
        //                    ret.password = split02[1];
        //                    break;
        //                default:
        //                    if (null != s && s != ";" && s != string.Empty)
        //                        ret.ex += s + ";";//split02[0] + "=" + split02[1] + ";";
        //                    break;
        //            }
        //        }
        //        catch (Exception ex)
        //        {
        //            System.Diagnostics.Debug.WriteLine(ex.Message);

        //        }
        //    }
        //    ret.is_dirty = true;
        //    string ret_s = ret.ConnectionString;
        //    return ret;
        //}
#endregion

#region old create sql
//#region CreateConnection
///// <summary>
///// Get an open instance of an IDbConnection pointed at this ConnectionString.
///// </summary>
///// <returns>An Open IDbConnection or null.</returns>
//public IDbConnection CreateConnection()
//{
//    IDbConnection connDb = null;
//    try
//    {	//don't waste ticks on an IsValid check
//        connDb = new IDbConnection(ConnectionString);
//        connDb.Open();
//    }
//    catch (Exception ex)
//    {
//        System.Diagnostics.Debug.WriteLine(ex.Message);//return null if error
//        connDb = null;
//        throw ex;
//    }
//    return connDb;
//}
//#endregion
//#region CreateCommand
///// <summary>
///// Gets a IDbCommand object initialized to this connection.
///// </summary>
///// <returns>A IDbCommand or null.</returns>
//public IDbCommand CreateCommand()
//{
//    IDbCommand cmd = null;

//    try
//    {
//        IDbConnection conn = CreateConnection();

//        if (null == conn)
//            return cmd;

//        cmd = new IDbCommand(string.Empty, conn);
//    }
//    catch (Exception ex)
//    {
//        System.Diagnostics.Debug.WriteLine(ex.Message);//return null if error
//        cmd = null;
//    }

//    return cmd;
//}
///// <summary>
///// Gets a IDbCommand object initialized to this connection and set to the in passed command.
///// </summary>
///// <param name="command_text">The sql, table name or stored procedure name to use.</param>
///// <param name="type">The type of command text passed in.</param>
///// <returns>A IDbCommand or null.</returns>
//public IDbCommand CreateCommand(string command_text, CommandType type)
//{
//    IDbConnection conn = CreateConnection();
//    IDbCommand cmd = null;

//    if (null == conn)
//        return cmd;

//    cmd = new IDbCommand(command_text, conn);
//    cmd.CommandType = type;

//    return cmd;
//}

//public IDbCommand CreateCommand(string command_text, CommandType type, bool bindByName)
//{
//    IDbConnection conn = CreateConnection();
//    IDbCommand cmd = null;

//    if (null == conn)
//        return cmd;

//    cmd = new IDbCommand(command_text, conn);

//    cmd.CommandType = type;
//    cmd.BindByName = bindByName;

//    return cmd;
//}

///// <summary>
///// Gets a IDbCommand initialized to this connection and set to use the in passed procedure.
///// </summary>
///// <param name="procedure_name">Stored procedure name.</param>
///// <param name="param">Parameter to be used by the procedure.</param>
///// <returns>A IDbCommand or null.</returns>
//public IDbCommand CreateCommand(string procedure_name, IDataParameter param)
//{
//    IDbCommand cmd = CreateCommand(procedure_name, CommandType.StoredProcedure);

//    if (null == cmd)
//        return cmd;

//    if (null != param)
//        cmd.Parameters.Add(param);

//    return cmd;
//}

///// <summary>
///// Gets a IDbCommand initialized to this connection and set to use the in passed procedure.
///// </summary>
///// <param name="procedure_name">Stored procedure name.</param>
///// <param name="param">Parameters to be used by the procedure.</param>
///// <returns>A IDbCommand or null.</returns>
//public IDbCommand CreateCommand(string procedure_name, IDataParameter[] param)
//{
//    IDbCommand cmd = CreateCommand(procedure_name, CommandType.StoredProcedure);

//    if (null == cmd)
//        return cmd;

//    if (param.Length > 0)
//    {
//        for (int i = 0; i < param.Length; i++)
//            cmd.Parameters.Add(param[i]);
//    }

//    return cmd;
//}
//#endregion
//#region CreateDataAdapter
///// <summary>
///// Creates a IDbDatAdapter initalized to this connection and set to use the in passed info.
///// </summary>
///// <param name="command_text">The sql, Table or Stored Procedure to be used.</param>
///// <param name="type">The type of command execution.</param>
///// <returns>A IDbDataAdapter or null.</returns>
//public IDbDataAdapter CreateDataAdapter(string command_text, CommandType type)
//{
//    IDbDataAdapter ret = null;

//    try
//    {
//        IDbCommand cmd = CreateCommand(command_text, type);

//        if (null == cmd)
//            return ret;

//        ret = new IDbDataAdapter(cmd);
//    }
//    catch (Exception ex)
//    {
//        System.Diagnostics.Debug.WriteLine(ex.Message);//return null if error
//        ret = null;
//    }
//    return ret;
//}
//public IDbDataAdapter CreateDataAdapter(string command_text, CommandType type, bool bindByName)
//{
//    IDbDataAdapter ret = null;

//    try
//    {
//        IDbCommand cmd = CreateCommand(command_text, type, bindByName);

//        if (null == cmd)
//            return ret;

//        ret = new IDbDataAdapter(cmd);
//    }
//    catch (Exception ex)
//    {
//        System.Diagnostics.Debug.WriteLine(ex.Message);//return null if error
//        ret = null;
//    }
//    return ret;
//}
///// <summary>
///// Creates a IDbDatAdapter initalized to this connection and set to use the in passed info.
///// </summary>
///// <param name="procedure_name">Stored procedure name.</param>
///// <param name="param">Parameter to be used by the procedure.</param>
///// <returns>A IDbDataAdapter or null.</returns>
//public IDbDataAdapter CreateDataAdapter(string procedure, IDataParameter param)
//{
//    IDbDataAdapter ret = null;

//    try
//    {
//        IDbCommand cmd = CreateCommand(procedure, param);

//        if (null == cmd)
//            return ret;

//        ret = new IDbDataAdapter(cmd);
//    }
//    catch (Exception ex)
//    {
//        System.Diagnostics.Debug.WriteLine(ex.Message);//return null if error
//        ret = null;
//    }
//    return ret;
//}
///// <summary>
///// Creates a IDbDatAdapter initalized to this connection and set to use the in passed info.
///// </summary>
///// <param name="procedure_name">Stored procedure name.</param>
///// <param name="param">Parameters to be used by the procedure.</param>
///// <returns>A IDbDataAdapter or null.</returns>
//public IDbDataAdapter CreateDataAdapter(string procedure, IDataParameter[] param)
//{
//    IDbDataAdapter ret = null;

//    try
//    {
//        IDbCommand cmd = CreateCommand(procedure, param);

//        if (null == cmd)
//            return ret;

//        ret = new IDbDataAdapter(cmd);
//    }
//    catch (Exception ex)
//    {
//        System.Diagnostics.Debug.WriteLine(ex.Message);//return null if error
//        ret = null;
//    }
//    return ret;
//}
//#endregion
//#region CreateParameter
//public DbType GetDbType(object value)
//{

//    DbType type = DbType.String;
//    string typeName = value.GetType().FullName;
//    switch (typeName)
//    {
//        case "System.Int16":
//            type = DbType.Int16;
//            break;
//        case "System.Int32":
//            type = DbType.Int32;
//            break;
//        case "System.Int64":
//            type = DbType.Int64;
//            break;
//        case "System.UInt16":
//            type = DbType.UInt16;
//            break;
//        case "System.UInt32":
//            type = DbType.UInt32;
//            break;
//        case "System.UInt64":
//            type = DbType.UInt64;
//            break;
//        case "System.Decimal":
//            type = DbType.Decimal;
//            break;
//        case "System.Double":
//            type = DbType.Double;
//            break;
//        case "System.Single":
//            type = DbType.Single;
//            break;
//        case "System.String":
//            type = DbType.String;
//            break;
//        case "System.DateTime":
//            type = DbType.DateTime;
//            break;
//        case "System.Byte[]":
//            type =  DbType.Binary;
//            break;
//        case "System.Byte":
//            type = DbType.Byte;
//            break;
//        default:
//            throw new Exception("The in passed value is of a type that is not currently supported for dynamic determination.");
//    }

//    return type;
//}

//public IDataParameter CreateParameter(string name, object value, System.Data.ParameterDirection direction, DbType type)
//{
//    if (null == name || name.Trim().Length == 0) return null;
//    if (null == value) return null;

//    IDataParameter ret = new IDataParameter();

//    ret.ParameterName = name;
//    ret.Direction = direction;
//    ret.DbType = type;
//    ret.Value = value;

//    return ret;
//}

//public IDataParameter CreateParameter(string name, object value)
//{
//    if (null == name || name.Trim().Length == 0) return null;
//    if (null == value) return null;

//    DbType type = GetDbType(value);
//    return CreateParameter(name, value, ParameterDirection.Input, type);
//}
//#endregion


#endregion